package com.shsh.safemanage.dao;

import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.Department1PostcheckMapping;
import com.shsh.safemanage.model.PostCheck;
import com.shsh.safemanage.model.PostcheckContent;
import com.shsh.safemanage.model.PostcheckDailyReportItem;
import com.shsh.safemanage.model.PostcheckDepartMonthCheckSummaryReport;
import com.shsh.safemanage.model.PostcheckMonthCheckSummaryReport;
import com.shsh.safemanage.model.PostcheckMonthQualityReport;
import com.shsh.safemanage.model.PostcheckMonthSanweiReport;
import com.shsh.safemanage.model.PostcheckPattern;
import com.shsh.safemanage.model.PostcheckSource;
import com.shsh.safemanage.model.PostcheckStandard;
import com.shsh.safemanage.model.Problem;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.Util;
import com.shsh.safemanage.util.ValueUtil;

public class PostCheckDao {
	public List<PostcheckSource> getPostcheckSourceList() {
		String sql = "select ID, NAME from postcheck_source order by NAME";
		List<PostcheckSource> postcheckSourceList = new ArrayList<PostcheckSource>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckSource source = new PostcheckSource();
				source.setId(rs.getLong("ID"));
				source.setName(rs.getString("NAME"));

				postcheckSourceList.add(source);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return postcheckSourceList;
	}

	public List<PostCheck> getPostCheckList() {
		String sql = "select ID, CHECK_NAME from postcheck";
		List<PostCheck> postCheckList = new ArrayList<PostCheck>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostCheck postCheck = new PostCheck();
				postCheck.setId(rs.getLong("ID"));
				postCheck.setCheckName(rs.getString("CHECK_NAME"));

				postCheckList.add(postCheck);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return postCheckList;
	}

	public List<PostCheck> getPostCheckByIDArray(long[] ids) {
		if (ids == null) {
			return null;
		}
		if (ids.length == 0) {
			return new ArrayList<PostCheck>();
		}

		String inClause = "(" + Util.toString(ids, ",", "") + ")";
		String sql = "select ID, CHECK_NAME from postcheck where ID in " + inClause;

		Connection conn = null;
		PreparedStatement pstm = null;
		List<PostCheck> postcheckList = new ArrayList<PostCheck>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostCheck postcheck = new PostCheck();
				postcheck.setId(rs.getLong("ID"));
				postcheck.setCheckName(rs.getString("CHECK_NAME"));

				postcheckList.add(postcheck);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return postcheckList;
	}

	public PostCheck getPostCheck(Long id) {
		String sql = "select ID, CHECK_NAME from postcheck where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				PostCheck postcheck = new PostCheck();
				postcheck.setId(rs.getLong("ID"));
				postcheck.setCheckName(rs.getString("CHECK_NAME"));

				return postcheck;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public PostcheckPattern getPostcheckPatternByID(Long id) {
		String sql = "select ID, POSTCHECK_ID, PATTERN from postcheck_pattern where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				PostcheckPattern pattern = new PostcheckPattern();
				pattern.setId(rs.getLong("ID"));
				pattern.setPattern(rs.getString("PATTERN"));
				pattern.setPostcheckID(rs.getLong("POSTCHECK_ID"));

				return pattern;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public boolean addUserPostcheckMapping(User registerUser, Set<PostCheck> postcheckSet) {
		String selectSQL = "select ID from user_postcheck_mapping where USER_ID=? and POSTCHECK_ID=?";
		String sql = "insert into user_postcheck_mapping(USER_ID, POSTCHECK_ID) values(?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSQL);
			pstm1 = conn.prepareStatement(sql);
			for (PostCheck postcheck : postcheckSet) {
				pstm.setLong(1, registerUser.getId());
				pstm.setLong(2, postcheck.getId());

				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm1.setLong(1, registerUser.getId());
					pstm1.setLong(2, postcheck.getId());

					pstm1.executeUpdate();
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean deleteUserMappingPostchecks(Long userID) {
		String sql = "delete from user_postcheck_mapping where USER_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);
			pstm.executeUpdate();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public Set<PostCheck> getUserMappingPostcheck(Long userID) {
		String sql = "select POSTCHECK_ID, postcheck.CHECK_NAME "
				+ "from user_postcheck_mapping inner join postcheck on user_postcheck_mapping.POSTCHECK_ID=postcheck.ID "
				+ "where USER_ID=?";

		Set<PostCheck> set = new HashSet<PostCheck>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostCheck postCheck = new PostCheck();
				postCheck.setId(rs.getLong("POSTCHECK_ID"));
				postCheck.setCheckName(rs.getString("CHECK_NAME"));

				set.add(postCheck);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return set;
	}

	public List<PostcheckPattern> getPostcheckPatternByPostcheckID(Long postcheckID) {
		String sql = "select ID, POSTCHECK_ID, PATTERN from postcheck_pattern where POSTCHECK_ID = ? order by ID";

		List<PostcheckPattern> lst = new ArrayList<PostcheckPattern>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, postcheckID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckPattern pattern = new PostcheckPattern();
				pattern.setId(rs.getLong("ID"));
				pattern.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				pattern.setPattern(rs.getString("PATTERN"));

				lst.add(pattern);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public List<PostcheckContent> getPostcheckContentByPostcheckID(Long postcheckID) {
		String sql = "select ID, POSTCHECK_ID, POSTCHECK_PATTERN_ID, CONTENT from postcheck_content where POSTCHECK_ID = ? order by ID";

		List<PostcheckContent> lst = new ArrayList<PostcheckContent>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, postcheckID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckContent content = new PostcheckContent();
				content.setId(rs.getLong("ID"));
				content.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				content.setPostcheckPatternID(rs.getLong("POSTCHECK_PATTERN_ID"));
				content.setContent(rs.getString("CONTENT"));

				lst.add(content);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public List<PostcheckContent> getPostcheckContentByPostcheckPatternID(Long postcheckPatternID) {
		String sql = "select ID, POSTCHECK_ID, POSTCHECK_PATTERN_ID, CONTENT from postcheck_content where POSTCHECK_PATTERN_ID = ? order by ID";

		List<PostcheckContent> lst = new ArrayList<PostcheckContent>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, postcheckPatternID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckContent content = new PostcheckContent();
				content.setId(rs.getLong("ID"));
				content.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				content.setPostcheckPatternID(rs.getLong("POSTCHECK_PATTERN_ID"));
				content.setContent(rs.getString("CONTENT"));

				lst.add(content);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public PostcheckContent getPostcheckContentByID(Long postcheckContentID) {
		String sql = "select ID, POSTCHECK_ID, POSTCHECK_PATTERN_ID, CONTENT from postcheck_content where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, postcheckContentID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				PostcheckContent content = new PostcheckContent();
				content.setId(rs.getLong("ID"));
				content.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				content.setPostcheckPatternID(rs.getLong("POSTCHECK_PATTERN_ID"));
				content.setContent(rs.getString("CONTENT"));

				return content;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<PostcheckStandard> getPostcheckStandardByPostcheckContentID(Long postcheckContentID) {
		String sql = "select ID, POSTCHECK_ID, POSTCHECK_PATTERN_ID, POSTCHECK_CONTENT_ID, STANDARD, SOURCES, GRADE_STANDARD, CHECK_FREQUENCY from postcheck_standard where POSTCHECK_CONTENT_ID = ? order by ID";

		List<PostcheckStandard> lst = new ArrayList<PostcheckStandard>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, postcheckContentID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckStandard standard = new PostcheckStandard();
				standard.setId(rs.getLong("ID"));
				standard.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				standard.setPostcheckPatternID(rs.getLong("POSTCHECK_PATTERN_ID"));
				standard.setPostcheckContentID(rs.getLong("POSTCHECK_CONTENT_ID"));
				standard.setStandard(rs.getString("STANDARD"));
				standard.setGradeStandard(rs.getString("GRADE_STANDARD"));
				standard.setCheckFrequency(rs.getString("CHECK_FREQUENCY"));

				String sourceIDs = rs.getString("SOURCES");
				standard.setSources(Util.toLongArray(sourceIDs, ","));

				if (!Util.isNullOrEmpty(sourceIDs)) {
					standard.setSourceNames(getPostcheckSourceNames(sourceIDs));
				} else {
					standard.setSourceNames(new String[] {});
				}

				lst.add(standard);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public String[] getPostcheckSourceNames(String sourceIDs) {
		List<PostcheckSource> sources = getPostcheckSources(sourceIDs);
		String[] names = new String[sources.size()];
		for (int i = 0; i < sources.size(); i++) {
			names[i] = sources.get(i).getName();
		}
		return names;
	}

	public List<PostcheckSource> getPostcheckSources(String sourceIDs) {
		String sql = "select ID, NAME from postcheck_source where ID in (" + sourceIDs + ")";

		List<PostcheckSource> lst = new ArrayList<PostcheckSource>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostcheckSource standard = new PostcheckSource();
				standard.setId(rs.getLong("ID"));
				standard.setName(rs.getString("NAME"));

				lst.add(standard);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public boolean addPostcheckStandard(long postcheckID, long postcheckPatternID, long postcheckContentID, String standard,
			String sources, String gradeStandard, String checkFrequency) {
		String revertedSources = sources;
		if (sources != null && sources.length() > 0) {
			long[] ids = Util.toLongArray(sources, ",");
			Arrays.sort(ids);
			revertedSources = Util.toString(ids, ",", "");
		}

		String sql = "insert into postcheck_standard (POSTCHECK_ID, POSTCHECK_PATTERN_ID, POSTCHECK_CONTENT_ID, STANDARD, SOURCES, GRADE_STANDARD, CHECK_FREQUENCY) values (?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, postcheckID);
			pstm.setLong(2, postcheckPatternID);
			pstm.setLong(3, postcheckContentID);
			pstm.setString(4, standard);
			pstm.setString(5, revertedSources);
			pstm.setString(6, gradeStandard);
			pstm.setString(7, checkFrequency);

			pstm.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updatePostcheckStandard(long postcheckID, long postcheckPatternID, long postcheckContentID, long postcheckStandardID,
			String standard, String sources, String gradeStandard, String checkFrequency) {
		String revertedSources = sources;
		if (sources != null && sources.length() > 0) {
			long[] ids = Util.toLongArray(sources, ",");
			Arrays.sort(ids);
			revertedSources = Util.toString(ids, ",", "");
		}

		String sql = "update postcheck_standard set "
				+ "POSTCHECK_ID = ?, POSTCHECK_PATTERN_ID=?, POSTCHECK_CONTENT_ID=?, STANDARD=?, SOURCES=?, GRADE_STANDARD=?, CHECK_FREQUENCY=? "
				+ "where ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, postcheckID);
			pstm.setLong(2, postcheckPatternID);
			pstm.setLong(3, postcheckContentID);
			pstm.setString(4, standard);
			pstm.setString(5, revertedSources);
			pstm.setString(6, gradeStandard);
			pstm.setString(7, checkFrequency);
			pstm.setLong(8, postcheckStandardID);

			pstm.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean deletePostcheckStandardByID(long postcheckStandardID) {
		String sql = "delete from postcheck_standard where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);
			pstm.setLong(1, postcheckStandardID);

			pstm.executeUpdate();

			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public PostcheckStandard getPostcheckStandardByID(long postcheckStandardID) {
		String sql = "select ID, POSTCHECK_ID, POSTCHECK_PATTERN_ID, POSTCHECK_CONTENT_ID, STANDARD, SOURCES, GRADE_STANDARD, CHECK_FREQUENCY from postcheck_standard where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, postcheckStandardID);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				PostcheckStandard standard = new PostcheckStandard();
				standard.setId(rs.getLong("ID"));
				standard.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				standard.setPostcheckPatternID(rs.getLong("POSTCHECK_PATTERN_ID"));
				standard.setPostcheckContentID(rs.getLong("POSTCHECK_CONTENT_ID"));
				standard.setStandard(rs.getString("STANDARD"));

				String sourceIDs = rs.getString("SOURCES");
				standard.setSources(Util.toLongArray(sourceIDs, ","));

				if (!Util.isNullOrEmpty(sourceIDs)) {
					standard.setSourceNames(getPostcheckSourceNames(sourceIDs));
				} else {
					standard.setSourceNames(new String[] {});
				}

				standard.setGradeStandard(rs.getString("GRADE_STANDARD"));
				standard.setCheckFrequency(rs.getString("CHECK_FREQUENCY"));

				return standard;
			}

			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public Object[] generateSummaryReport(int year, int month, int monthEnd, Long postcheckID, Long department1ID) {
		Object[] report = new Object[monthEnd + 1];
		for (int day = 1; day <= monthEnd; day++) {
			report[day] = generateDailySummaryReport(year, month, day, postcheckID, department1ID);
		}
		return report;
	}

	public List<User> generateDailySummaryReport(int year, int month, int day, Long postcheckID, Long department1ID) {
		String dayStart = "" + year + "-" + month + "-" + day + " 00:00:00";
		String dayEnd = "" + year + "-" + month + "-" + day + " 23:59:59";
		String sql = "  select e.ID, e.NAME from employee as e where e.DEPARTMENT1_ID = ? and e.id in ("
				+ "select p.WRITE_USER_ID from problem as p " + "where p.PROBLEM_TYPE = " + Constants.PROBLEM_TYPE_POSTCHECK
				+ " and p.POSTCHECK_ID = ? and p.WRITE_TIME >= '" + dayStart + "' and p.WRITE_TIME <= '" + dayEnd
				+ "' group by p.WRITE_USER_ID)";

		List<User> dailyReport = new ArrayList<User>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);
			pstm.setLong(2, postcheckID);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setDepartment1ID(department1ID);

				dailyReport.add(user);
			}

			return dailyReport;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return dailyReport;
	}

	public List<PostcheckDailyReportItem> generateUserDailyReport(Long postcheckID, Long department1ID, Long userID, int year, int month,
			int dayStart, int dayEnd) {
		String dayStartStr = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String dayEndStr = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = " select pp.PATTERN, pc.CONTENT, ps.STANDARD, ps.SOURCES, ps.GRADE_STANDARD, ps.CHECK_FREQUENCY, "
				+ " p.ID as PROBLEM_ID, p.PROBLEM_TITLE, p.PROBLEM_CONTENT, p.PROBLEM_ZHENGGAI_CUOSHI, "
				+ " we.DEPARTMENT1_ID as WRITE_DEPARTMENT1_ID, we.DEPARTMENT1_NAME as WRITE_DEPARTMENT1_NAME, we.NAME as WRITE_NAME, "
				+ " d1.DEPARTMENT1_NAME, d2.DEPARTMENT2_NAME as EQUIP_NAME, p.COMPLETE_DATE, p.CLOSE_TIME, p.WRITE_TIME, "
				+ " ce.NAME as CLOSE_NAME, p.GRADE, p.PROCESS_STATUS "
				+ " from "
				+ " problem as p "
				+ " inner join department1 as d1 on d1.ID = p.DEPARTMENT1_ID "
				+ " inner join department2 as d2 on d2.EQUIP_ID = p.EQUIPMENT_ID "
				+ " inner join (select e.ID, e.DEPARTMENT1_ID, e.NAME, ed1.DEPARTMENT1_NAME from employee as e inner join department1 as ed1 on e.DEPARTMENT1_ID = ed1.ID and e.DEPARTMENT1_ID = ? ) as we on we.ID = p.WRITE_USER_ID "
				+ " left outer join employee as ce on ce.ID = p.CLOSE_USER_ID "
				+ " inner join postcheck_pattern as pp on pp.ID = p.POSTCHECK_PATTERN_ID "
				+ " inner join postcheck_content as pc on pc.ID = p.POSTCHECK_CONTENT_ID "
				+ " inner join postcheck_standard as ps on ps.ID = p.POSTCHECK_STANDARD_ID " + " where  " + " p.PROBLEM_TYPE="
				+ Constants.PROBLEM_TYPE_POSTCHECK + " and p.POSTCHECK_ID = ?  " + " and p.WRITE_TIME<='" + dayEndStr
				+ "' and p.WRITE_TIME >= '" + dayStartStr + "' ";

		if (userID != null && userID > 0) {
			sql += " and p.WRITE_USER_ID = " + userID + " ";
		}
		sql += " order by p.ID desc ";

		List<PostcheckDailyReportItem> dailyReport = new ArrayList<PostcheckDailyReportItem>();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);
			pstm.setLong(2, postcheckID);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				PostcheckDailyReportItem item = new PostcheckDailyReportItem();

				// Post check pattern
				PostcheckPattern pattern = new PostcheckPattern();
				pattern.setPattern(rs.getString("PATTERN"));
				item.setPattern(pattern);

				// Post check content
				PostcheckContent content = new PostcheckContent();
				content.setContent(rs.getString("CONTENT"));
				item.setContent(content);

				// post check standard
				PostcheckStandard standard = new PostcheckStandard();
				standard.setStandard(rs.getString("STANDARD"));
				String sourceIDs = rs.getString("SOURCES");
				standard.setSources(Util.toLongArray(sourceIDs, ","));
				if (!Util.isNullOrEmpty(sourceIDs)) {
					standard.setSourceNames(getPostcheckSourceNames(sourceIDs));
				} else {
					standard.setSourceNames(new String[] {});
				}
				standard.setGradeStandard(rs.getString("GRADE_STANDARD"));
				standard.setCheckFrequency(rs.getString("CHECK_FREQUENCY"));

				// problem
				Problem problem = new Problem();
				problem.setId(rs.getLong("PROBLEM_ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setWriteUserName(rs.getString("WRITE_NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setCloseUserName(rs.getString("CLOSE_NAME"));
				problem.setGrade(rs.getInt("GRADE"));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				item.setProblem(problem);

				item.setStandard(standard);

				item.setWriteUserDepart1ID(rs.getLong("WRITE_DEPARTMENT1_ID"));
				item.setWriteUserDepart1Name(rs.getString("WRITE_DEPARTMENT1_NAME"));

				dailyReport.add(item);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return dailyReport;
	}

	public List<Problem> generateMonthCheckSummaryReport(Long[] postcheckIDs, Long[] depart1IDs, int year, int month, int dayStart,
			int dayEnd) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";
		String sql = " select p.ID, p.DEPARTMENT1_ID, p.POSTCHECK_ID, p.GRADE"
				+ " from problem as p left outer join department1 as d1 on d1.ID = p.DEPARTMENT1_ID where p.PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_POSTCHECK + " and p.IS_SOLVED <> 1 and p.DEPARTMENT1_ID in "
				+ Util.generateInCluse(depart1IDs, false) + " and p.POSTCHECK_ID in " + Util.generateInCluse(postcheckIDs, false)
				+ "  and p.WRITE_TIME >= '" + timeStart + "' and p.WRITE_TIME <= '" + timeEnd + "'";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				Problem p = new Problem();
				p.setId(rs.getLong("ID"));
				p.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				p.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				Object obj = rs.getObject("GRADE");
				if (obj != null) {
					p.setGrade((Integer) obj);
				}

				problemList.add(p);
			}

			return problemList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> generateDepartMonthSanweiReport(Long writeDepart1ID, String writeDepart1Name, int year, int month, int dayStart,
			int dayEnd, Boolean isSanwei) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = " select  p.ID, p.GRADE, p.DEPARTMENT1_ID, p.POSTCHECK_ID, p.EQUIPMENT_ID, d1.DEPARTMENT1_NAME, d2.DEPARTMENT2_NAME, "
				+ " p.PROBLEM_CONTENT, p.CLOSE_USER_ID, pc.CHECK_NAME  " + " from problem as p "
				+ " inner join department1 as d1 on d1.ID = p.DEPARTMENT1_ID "
				+ " left outer join department2 as d2 on d2.EQUIP_ID = p.EQUIPMENT_ID "
				+ " left outer join postcheck as pc on pc.ID = p.POSTCHECK_ID" + " where p.PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_POSTCHECK;

		if (isSanwei != null) {
			sql = sql + " and p.IS_SANWEI = " + (isSanwei ? "1" : "0");
		}

		sql = sql + " and p.WRITE_USER_DEPARTMENT1_ID = ? and p.WRITE_TIME >= '" + timeStart + "' and p.WRITE_TIME <= '" + timeEnd + "'"
				+ " order by p.POSTCHECK_ID ASC, p.DEPARTMENT1_ID asc, p.EQUIPMENT_ID asc";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, writeDepart1ID);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				Problem p = new Problem();
				p.setId(rs.getLong("ID"));
				p.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				p.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				Object obj = rs.getObject("GRADE");
				if (obj != null) {
					p.setGrade((Integer) obj);
				}
				p.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				p.setEquipmentName(rs.getString("DEPARTMENT2_NAME"));
				p.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				Object obj2 = rs.getObject("CLOSE_USER_ID");
				if (obj2 != null) {
					p.setCloseUserID(new Long((Integer) obj2));
				}
				p.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				p.setPostcheckName(rs.getString("CHECK_NAME"));

				p.setWriterDepartment1ID(writeDepart1ID);
				p.setWriterDepartment1Name(writeDepart1Name);

				problemList.add(p);
			}

			return problemList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> generateDepartMonthCheckSummaryReport(Long depart1ID, int year, int month, int dayStart, int dayEnd,
			boolean isSolved) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = " select p.ID, p.GRADE, p.DEPARTMENT1_ID, p.POSTCHECK_ID, p.EQUIPMENT_ID, d2.DEPARTMENT2_NAME, p.PROBLEM_CONTENT, p.CLOSE_USER_ID, pc.CHECK_NAME  "
				+ " from problem as p left outer join department2 as d2 on d2.EQUIP_ID = p.EQUIPMENT_ID "
				+ "  left outer join department1 as d1 on d1.ID = p.WRITE_USER_DEPARTMENT1_ID"
				+ " left outer join postcheck as pc on pc.ID = p.POSTCHECK_ID"
				+ " where p.PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_POSTCHECK
				+ "  and d1.DEPARTMENT_TYPE = "
				+ Constants.DEPARTMENT_TYPE_JIGUAN
				+ " and p.IS_SOLVED = "
				+ (isSolved ? "1" : "0")
				+ (isSolved ? " " : " and p.GRADE > 0 ")
				+ " and p.DEPARTMENT1_ID = ? and p.WRITE_TIME >= '"
				+ timeStart
				+ "' and p.WRITE_TIME <= '"
				+ timeEnd
				+ "'"
				+ " order by d2.ID ASC";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				Problem p = new Problem();
				p.setId(rs.getLong("ID"));
				p.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				Object obj = rs.getObject("GRADE");
				if (obj != null) {
					p.setGrade((Integer) obj);
				}
				p.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				p.setEquipmentName(rs.getString("DEPARTMENT2_NAME"));
				p.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				Object obj2 = rs.getObject("CLOSE_USER_ID");
				if (obj2 != null) {
					p.setCloseUserID(new Long((Integer) obj2));
				}
				p.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				p.setPostcheckName(rs.getString("CHECK_NAME"));

				problemList.add(p);
			}

			return problemList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> generateDepartMonthCheckSummaryReport(Long depart1ID, int year, int month, int dayStart, int dayEnd) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = " select p.ID, p.GRADE, p.DEPARTMENT1_ID, p.POSTCHECK_ID, p.EQUIPMENT_ID, d2.DEPARTMENT2_NAME, p.PROBLEM_CONTENT, p.CLOSE_USER_ID, p.WRITE_USER_DEPARTMENT1_ID, pc.CHECK_NAME  "
				+ " from problem as p left outer join department2 as d2 on d2.EQUIP_ID = p.EQUIPMENT_ID "
				+ " left outer join postcheck as pc on pc.ID = p.POSTCHECK_ID"
				+ " where p.PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_POSTCHECK
				+ " and p.DEPARTMENT1_ID = ? and p.IS_SOLVED <> 1 and p.GRADE>0 and p.WRITE_TIME >= '"
				+ timeStart
				+ "' and p.WRITE_TIME <= '" + timeEnd + "'" + " order by d2.ID ASC";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				Problem p = new Problem();
				p.setId(rs.getLong("ID"));
				p.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				Object obj = rs.getObject("GRADE");
				if (obj != null) {
					p.setGrade((Integer) obj);
				}
				p.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				p.setEquipmentName(rs.getString("DEPARTMENT2_NAME"));
				p.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				Object obj2 = rs.getObject("CLOSE_USER_ID");
				if (obj2 != null) {
					p.setCloseUserID(new Long((Integer) obj2));
				}
				p.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				p.setPostcheckName(rs.getString("CHECK_NAME"));

				p.setWriterDepartment1ID(rs.getLong("WRITE_USER_DEPARTMENT1_ID"));

				problemList.add(p);
			}

			return problemList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public boolean addDepartment1PostcheckMapping(Long depart1ID, Long postcheckID) {
		String sql = "insert into department1_postcheck_mapping(DEPARTMENT1_ID, POSTCHECK_ID) values(?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, depart1ID);
			pstm.setLong(2, postcheckID);

			conn.setAutoCommit(false);
			pstm.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);

		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public boolean deleteDepartment1PostcheckMapping(Long depart1ID, Long postcheckID) {
		String sql = "delete from department1_postcheck_mapping where DEPARTMENT1_ID = ? and POSTCHECK_ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, depart1ID);
			pstm.setLong(2, postcheckID);

			conn.setAutoCommit(false);
			pstm.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);

		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public List<Department1PostcheckMapping> searchDepartment1PostcheckMapping(Long depart1ID, Long postcheckID) {
		String sql = " select dpm.DEPARTMENT1_ID, dpm.POSTCHECK_ID, d1.DEPARTMENT1_NAME, pc.CHECK_NAME "
				+ " from department1_postcheck_mapping as dpm left outer join department1 as d1 on d1.ID = dpm.DEPARTMENT1_ID "
				+ " left outer join postcheck as pc on pc.ID = dpm.POSTCHECK_ID ";
		boolean hasPre = false;

		if (depart1ID != null && depart1ID > 0) {
			sql = sql + " where DEPARTMENT1_ID = " + depart1ID + " ";
			hasPre = true;
		}

		if (postcheckID != null && postcheckID > 0) {
			if (hasPre) {
				sql = sql + " and ";
			} else {
				sql = sql + " where ";
			}

			sql = sql + " POSTCHECK_ID = " + postcheckID + " ";
		}

		sql += " order by POSTCHECK_ID asc, d1.DEPARTMENT_TYPE desc, DEPARTMENT1_ID asc ";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<Department1PostcheckMapping> lst = new ArrayList<Department1PostcheckMapping>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1PostcheckMapping mapping = new Department1PostcheckMapping();
				mapping.setDepart1ID(rs.getLong("DEPARTMENT1_ID"));
				mapping.setDepart1Name(rs.getString("DEPARTMENT1_NAME"));
				mapping.setPostcheckID(rs.getLong("POSTCHECK_ID"));
				mapping.setPostcheckName(rs.getString("CHECK_NAME"));

				lst.add(mapping);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	/**
	 * 
	 * @param year
	 * @param month
	 * @param depart1ID
	 * @return if depart1ID = NULL, return corporation's report.
	 */
	public PostcheckDepartMonthCheckSummaryReport getMonthCheckSummaryReportByProblemID(Long problemID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[CHECK_REPORT] FROM [postcheck_month_check_report] "
				+ " where PROBLEM_ID = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckDepartMonthCheckSummaryReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getDepartCheckReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public boolean addMonthCheckSummaryReport(PostcheckMonthCheckSummaryReport report) {
		String sql = " insert into [postcheck_month_check_report] ([PROBLEM_ID] ,[YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[CHECK_REPORT]) VALUES (?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setNull(1, Types.INTEGER);
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());
			pstm.setNull(4, Types.INTEGER);

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(5, bytes);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public PostcheckMonthCheckSummaryReport getMonthCheckSummaryReport(int year, int month) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[CHECK_REPORT] FROM  [postcheck_month_check_report] "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID is null";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckMonthCheckSummaryReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getCorpCheckReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public boolean hasMonthCheckSummaryReport(int year, int month, Long depart1ID) {
		String sql = "select count([ID]) REPORT_COUNT FROM  [postcheck_month_check_report] "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			if (depart1ID == null) {
				pstm.setNull(3, Types.INTEGER);
			} else {
				pstm.setLong(3, depart1ID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				int reportCount = rs.getInt("REPORT_COUNT");
				return reportCount > 0;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public PostcheckMonthSanweiReport getMonthSanweiReportByProblemID(Long problemID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[SANWEI_REPORT] FROM  [postcheck_month_sanwei_report] "
				+ " where PROBLEM_ID = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckMonthSanweiReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getSanweiReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public PostcheckMonthSanweiReport getCorpMonthSanweiReport(int year, int month) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[SANWEI_REPORT] FROM  [postcheck_month_sanwei_report] "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID is null ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckMonthSanweiReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getSanweiReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	/**
	 * 
	 * @param year
	 * @param month
	 * @param depart1ID
	 * @return if depart1ID = NULL, return corporation's report.
	 */
	public PostcheckMonthSanweiReport getDepartMonthSanweiReport(int year, int month, Long depart1ID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[SANWEI_REPORT] FROM  [postcheck_month_sanwei_report] "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckMonthSanweiReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			if (depart1ID == null) {
				pstm.setNull(3, Types.INTEGER);
			} else {
				pstm.setLong(3, depart1ID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getSanweiReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	/**
	 * 
	 * @param year
	 * @param month
	 * @param depart1ID
	 * @return if depart1ID = NULL, return corporation's report.
	 */
	public PostcheckDepartMonthCheckSummaryReport getMonthCheckSummaryReport(int year, int month, Long depart1ID) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[DEPARTMENT1_ID]  ,[CHECK_REPORT] FROM  [postcheck_month_check_report] "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckDepartMonthCheckSummaryReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);
			if (depart1ID == null) {
				pstm.setNull(3, Types.INTEGER);
			} else {
				pstm.setLong(3, depart1ID);
			}

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getDepartCheckReportFromRS(rs);
				Long problemID = rs.getLong("PROBLEM_ID");
				report.setProblemID(problemID);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	public boolean updateCorpMonthSanweiReport(PostcheckMonthSanweiReport report) {
		String sql = " update [postcheck_month_sanwei_report] set PROBLEM_ID = ?, [YEAR]=? ,[MONTH] =? , [SANWEI_REPORT]=? "
				+ " where YEAR = ? and MONTH = ? and DEPARTMENT1_ID is null";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);
			if (report.getProblemID() != null) {
				pstm.setLong(1, report.getProblemID());
			} else {
				pstm.setNull(1, Types.INTEGER);
			}
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(4, bytes);

			pstm.setInt(5, report.getYear());
			pstm.setInt(6, report.getMonth());

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateMonthSanweiReport(Long problemID, PostcheckMonthSanweiReport report) {
		String sql = " update [postcheck_month_sanwei_report] set [YEAR]=? ,[MONTH] =? ,[DEPARTMENT1_ID]=? , [SANWEI_REPORT]=? where PROBLEM_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			report.setProblemID(problemID);

			pstm.setInt(1, report.getYear());
			pstm.setInt(2, report.getMonth());

			if (report.getDepart1ID() != null) {
				pstm.setLong(3, report.getDepart1ID());
			} else {
				pstm.setNull(3, Types.INTEGER);
			}

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(4, bytes);

			if (problemID != null) {
				pstm.setLong(5, problemID);
			} else {
				pstm.setNull(5, Types.INTEGER);
			}

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean updateMonthCheckSummaryReport(Long problemID, PostcheckDepartMonthCheckSummaryReport report) {
		String sql = " update [postcheck_month_check_report] set [YEAR]=? ,[MONTH] =? ,[DEPARTMENT1_ID]=? , [CHECK_REPORT]=? where PROBLEM_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, report.getYear());
			pstm.setInt(2, report.getMonth());
			if (report.getDepart1ID() != null) {
				pstm.setLong(3, report.getDepart1ID());
			} else {
				pstm.setNull(3, Types.INTEGER);
			}

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(4, bytes);

			pstm.setLong(5, problemID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			report.setProblemID(problemID);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addCorpMonthSanweiReport(Long problemID, PostcheckMonthSanweiReport report) {
		String sql = " insert into [postcheck_month_sanwei_report] ([PROBLEM_ID] ,[YEAR] ,[MONTH] ,[SANWEI_REPORT]) VALUES (?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			if (problemID != null) {
				pstm.setLong(1, problemID);
			} else {
				pstm.setNull(1, Types.INTEGER);
			}
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(4, bytes);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public boolean addMonthSanweiReport(Long problemID, PostcheckMonthSanweiReport report) {
		String sql = " insert into [postcheck_month_sanwei_report] ([PROBLEM_ID] ,[YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[SANWEI_REPORT]) VALUES (?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			if (problemID != null) {
				pstm.setLong(1, problemID);
			} else {
				pstm.setNull(1, Types.INTEGER);
			}
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());

			if (report.getDepart1ID() != null) {
				pstm.setLong(4, report.getDepart1ID());
			} else {
				pstm.setNull(4, Types.INTEGER);
			}

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(5, bytes);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public boolean addMonthCheckSummaryReport(Long problemID, PostcheckDepartMonthCheckSummaryReport report) {
		String sql = " insert into [postcheck_month_check_report] ([PROBLEM_ID] ,[YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[CHECK_REPORT]) VALUES (?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setLong(1, problemID);
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());
			pstm.setLong(4, report.getDepart1ID());

			byte[] bytes = Util.getBytes(report);
			pstm.setObject(5, bytes);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			report.setProblemID(problemID);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}

			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	private PostcheckMonthCheckSummaryReport getCorpCheckReportFromRS(ResultSet rs) {
		ObjectInputStream in = null;

		PostcheckMonthCheckSummaryReport report = null;
		try {
			Blob reportBlob = rs.getBlob("CHECK_REPORT");

			in = new ObjectInputStream(reportBlob.getBinaryStream());

			report = (PostcheckMonthCheckSummaryReport) in.readObject();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (Exception e) {
			}
		}
		return report;
	}

	private PostcheckMonthQualityReport getQualityReportFromRS(ResultSet rs) {
		ObjectInputStream in = null;

		PostcheckMonthQualityReport report = null;
		try {
			Blob reportBlob = rs.getBlob("QUALITY_REPORT");

			in = new ObjectInputStream(reportBlob.getBinaryStream());

			report = (PostcheckMonthQualityReport) in.readObject();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (Exception e) {
			}
		}
		return report;
	}

	private PostcheckMonthSanweiReport getSanweiReportFromRS(ResultSet rs) {
		ObjectInputStream in = null;

		PostcheckMonthSanweiReport report = null;
		try {
			Blob reportBlob = rs.getBlob("SANWEI_REPORT");

			in = new ObjectInputStream(reportBlob.getBinaryStream());

			report = (PostcheckMonthSanweiReport) in.readObject();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (Exception e) {
			}
		}
		return report;
	}

	public boolean updateMonthQualityReport(PostcheckMonthQualityReport report) {
		String sql = "update  [postcheck_month_quality_report] set QUALITY_REPORT = ? where YEAR =? and MONTH = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setBytes(1, Util.getBytes(report));
			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public boolean addMonthQualityReport(Long problemID, PostcheckMonthQualityReport report) {
		String sql = "insert into  [postcheck_month_quality_report] (PROBLEM_ID, YEAR, MONTH, QUALITY_REPORT) values(?, ?, ?, ?) ";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			if (problemID != null) {
				pstm.setLong(1, problemID);
			} else {
				pstm.setNull(1, Types.INTEGER);
			}

			pstm.setInt(2, report.getYear());
			pstm.setInt(3, report.getMonth());
			pstm.setBytes(4, Util.getBytes(report));

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public PostcheckMonthQualityReport getMonthQualityReport(int year, int month) {
		String sql = "select [ID] ,[PROBLEM_ID], [YEAR]  ,[MONTH] ,[QUALITY_REPORT] FROM  [postcheck_month_quality_report] "
				+ " where YEAR = ? and MONTH = ? ";
		Connection conn = null;
		PreparedStatement pstm = null;
		PostcheckMonthQualityReport report = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, year);
			pstm.setInt(2, month);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				report = getQualityReportFromRS(rs);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return report;
	}

	private PostcheckDepartMonthCheckSummaryReport getDepartCheckReportFromRS(ResultSet rs) {
		ObjectInputStream in = null;

		PostcheckDepartMonthCheckSummaryReport report = null;
		try {
			Blob reportBlob = rs.getBlob("CHECK_REPORT");

			in = new ObjectInputStream(reportBlob.getBinaryStream());

			report = (PostcheckDepartMonthCheckSummaryReport) in.readObject();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (Exception e) {
			}
		}
		return report;
	}

	public int getCheckUnitCount(int year, int month, int dayStart, int dayEnd, Long depart1ID) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = "select COUNT(*) CHECK_UNIT_COUNT from problem where PROBLEM_TYPE =1 and IS_SOLVED = 0 and DEPARTMENT1_ID =  "
				+ depart1ID + "  and WRITE_TIME >= '" + timeStart + "' and WRITE_TIME <= '" + timeEnd + "'";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt("CHECK_UNIT_COUNT");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}

	public List<Problem> getSelfCheckProblem(int year, int month, int dayStart, int dayEnd, Long depart1ID, Long postcheckID) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = "select ID, GRADE from problem where PROBLEM_TYPE =1 and IS_SOLVED = 0 and GRADE>0 and POSTCHECK_ID = " + postcheckID
				+ " and DEPARTMENT1_ID = WRITE_USER_DEPARTMENT1_ID and DEPARTMENT1_ID =  " + depart1ID + "  and WRITE_TIME >= '"
				+ timeStart + "' and WRITE_TIME <= '" + timeEnd + "'";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<Problem> problemList = new ArrayList<Problem>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem p = new Problem();
				p.setId(rs.getLong("ID"));
				p.setGrade(rs.getInt("GRADE"));

				problemList.add(p);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public int getChushiMantime(int year, int month, int dayStart, int dayEnd, Long depart1ID) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = "select COUNT(*) MANTIME from problem where PROBLEM_TYPE =1 and WRITE_USER_DEPARTMENT1_ID =  " + depart1ID
				+ "  and WRITE_TIME >= '" + timeStart + "' and WRITE_TIME <= '" + timeEnd + "'";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt("MANTIME");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}

	public int getShiyebuMantime(int year, int month, int dayStart, int dayEnd, Long depart1ID) {
		String timeStart = "" + year + "-" + month + "-" + dayStart + " 00:00:00";
		String timeEnd = "" + year + "-" + month + "-" + dayEnd + " 23:59:59";

		String sql = "select COUNT(*) MANTIME from problem where PROBLEM_TYPE =1 and DEPARTMENT1_ID =  " + depart1ID
				+ "  and WRITE_TIME >= '" + timeStart + "' and WRITE_TIME <= '" + timeEnd + "'";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt("MANTIME");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}
}
